package com.person.jc.service.test.inspect;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.common.SQLSpell;
import com.person.jc.service.Service;
import com.person.jc.utils.DateUtils;
import com.person.jc.utils.Utils;

public class BsRepairRegLogService extends Service {
	
	public static BsRepairRegLogService me = new BsRepairRegLogService();
	private static String optMenu = "我的检查任务";
	
	public List<Record> getList(Map<String, Object> map) {
		String temp = "";
		
		String tabName = "";
		String selectExt="";
		if("repair".equals(map.get("flag"))){
			 selectExt="    ,ifnull(sum(brl.original_record_ok),0) num3\n";
			tabName = "left join bs_recondition_log brl ON oe.equ_id = brl.equ_id  and brl.original_record is not null  and brl.original_record_ok !='0'\n 	inner join bs_repair_confirm_log brcl on rl.log_id = brcl.log_id and brcl.confirm_status=1 \n ";
		}else if("testing".equals(map.get("flag"))){
			selectExt="    ,ifnull(sum(brl.original_record_ok),0) num3\n";
			tabName = "left join bs_recondition_log brl ON oe.equ_id = brl.equ_id  and brl.original_record is not null  and brl.original_record_ok !='0' 	 \n ";
		}
		List<Object> params = new ArrayList<Object>();
		String tempSql = "SELECT\n" +
						"    o.order_id,\n" + 
						"    o.cust_id,\n" + 
						"	("+SQLSpell.customerName("o.cust_id")+") cust_name,\n" +
						"    oe.device_type,\n" + 
						"	("+SQLSpell.kindName("device_type", "oe.device_type")+") device_type_name,\n" +
						"    au.user_id,\n" + 
						"    COUNT(oe.device_no) num1,\n" + 
						"    COUNT(rl.log_id) num2\n" +
							selectExt +
						"FROM\n" +
						"    bs_task_assign a\n" + 
						"        LEFT JOIN\n" + 
						"    bs_task_assign_user au ON a.assign_id = au.assign_id\n" + 
						"        LEFT JOIN\n" + 
						"    bs_order_equipment oe ON au.order_id = oe.order_id AND au.device_type = oe.device_type\n" + 
						"        LEFT JOIN\n" + 
						"    bs_order o ON oe.order_id = o.order_id\n" + 
						"        LEFT JOIN\n" + 
						"    bs_repair_reg_log rl ON oe.equ_id = rl.equ_id\n" +
						"    "+tabName +
						"WHERE\n" +
						"    a.order_type = ?\n" +
						"    AND o.flow_id = ?\n";
						
		StringBuffer sql = new StringBuffer(tempSql);
		params.add(map.get("orderType"));
		params.add(map.get("flow"));
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("optUser")))){
			sql.append("AND au.user_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
			sql.append("AND o.cust_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
			sql.append("AND o.order_id like ?\n");
			params.add("%"+temp+"%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceType")))){
			sql.append("AND oe.device_type = ?\n");
			params.add(temp);
		}
		sql.append("GROUP BY o.order_id,o.cust_id , oe.device_type , au.user_id\n");
		sql.append("ORDER BY o.order_id,o.cust_id , oe.device_type");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}

	public List<Record> getViewList(Map<String, Object> map) {
		String temp = "";
		List<Object> params = new ArrayList<Object>();
		
		String tempSql = "SELECT DISTINCT\n" +
						"	 oe.*, rl.log_id, ("+SQLSpell.userName("rl.opt_user")+") repair_user, rd.*, rl.address, rl.check_remark, rl.repair_remark,rcl.original_record,\n" +
						"	 ("+SQLSpell.kindName("volume_category", "oe.volume_category")+") volume_category_,\n" +
						"	 ("+SQLSpell.kindName("device_type", "oe.device_type")+") device_type_,\n" +
						"	 (SELECT group_concat(t.device_name) from bd_device_info t where FIND_IN_SET(t.device_id, oe.gas_type)) gas_type_,\n" + 
						"	 ("+SQLSpell.kindName("yes_or_no", "oe.if_cert")+") if_cert_,\n" + 
						"	 rc.con_id, rc.confirm_status\n" + 
						"FROM\n" + 
						"	 bs_task_assign a\n" + 
						"        LEFT JOIN\n" + 
						"    bs_task_assign_user au ON a.assign_id = au.assign_id\n" + 
						"        LEFT JOIN\n" + 
						"    bs_order_equipment oe ON au.order_id = oe.order_id AND au.device_type = oe.device_type\n" + 
						"        LEFT JOIN\n" + 
						"    bs_order o ON oe.order_id = o.order_id\n" + 
						"        LEFT JOIN\n" + 
						"    bs_repair_reg_log rl ON oe.equ_id = rl.equ_id\n" + 
						"        LEFT JOIN\n" + 
						"    (select d.log_id, group_concat(distinct f.fault_name order by f.fault_name asc separator '; ') fault_name,group_concat(DISTINCT concat(p.parts_name, '[' , p.parts_code, ']') ORDER BY p.parts_name ASC, p.parts_code ASC separator '; ') parts_name from bs_repair_check_detail d left join bd_fault_info f on d.fault_id = f.fault_id left join bd_device_parts p on d.parts_id = p.parts_id group by d.log_id) rd on rd.log_id = rl.log_id\n" + 
						"        LEFT JOIN\n" + 
						"    bs_repair_confirm_log rc on rl.log_id = rc.log_id\n" +
						"			 LEFT JOIN\n" +
						"		bs_recondition_log rcl on rl.log_id = rcl.log_id\n " +
						"WHERE a.order_type = "+String.valueOf(map.get("orderType"))+"\n";
		StringBuffer sql = new StringBuffer(tempSql);
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("userId")))){
			sql.append("   AND au.user_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("optUser")))){
			sql.append("   AND rl.opt_user = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("custId")))){
			sql.append("AND o.cust_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("orderId")))){
			sql.append("AND o.order_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("serviceUser")))){
			sql.append("AND o.service_user = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("flowId")))){
			sql.append("AND o.flow_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceType")))){
			sql.append("AND oe.device_type = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("equId")))){
			sql.append("AND oe.equ_id = ?\n");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("deviceNo")))){
			if("eq".equals(map.get("opflag"))){
				sql.append("AND oe.device_no = ?\n");
				params.add(temp);
			}else{
				sql.append("AND oe.device_no like ?\n");
				params.add("%" + temp + "%");
				
			}
		}
		sql.append("ORDER BY o.cust_id, oe.order_id");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}

	public String save(Map<String, Object> map) {
		String sql = "select * from bs_repair_reg_log where equ_id = ?";
		List<Object> list = Db.query(sql, map.get("equId"));
		if(list!=null && list.size()>0){
			return "exist";
		}
		
		Record record = new Record();
		record.set("order_id", map.get("orderId"));
		record.set("cust_id", map.get("custId"));
		record.set("equ_id", map.get("equId"));
		record.set("device_type", map.get("deviceType"));
		record.set("check_user", map.get("checkUser"));
		record.set("address", map.get("address"));
		record.set("check_remark", map.get("checkRemark"));
		record.set("repair_remark", map.get("repairRemark"));
		record.set("opt_user", map.get("optUser"));
		record.set("opt_date", DateUtils.getDateTime());
		Db.save("bs_repair_reg_log", record);
		
		String idxSql = "SELECT LAST_INSERT_ID() from dual ";
		BigInteger logId = Db.queryColumn(idxSql);
		super.setCRUD(optMenu, "C", "新增检测记录：" + logId);
		return String.valueOf(logId);
	}

	public void saveDetail(String logId, Object[][] params, int length) {
		String sql = "INSERT INTO bs_repair_check_detail (log_id, fault_id, parts_id) VALUES (?, ?, ?)";
		Db.batch(sql, params, length);
		super.setCRUD(optMenu, "C", "新增检测记录明细：" + logId);
	}
	
	
	/**
	 * 初始化更换配件 故障及配件信息
	 * @作者 : 王春林（chunlin.wang@51durian.com）
	 * @日期 : 2017年2月7日
	 * @param logId
	 * @return
	 */
	public List<Record> getFaultPartsList(String logId) {
		String sql = "SELECT\n" +
				"	d.log_id,\n" +
				"	d.fault_id,\n" +
				"	group_concat(CONCAT(d.parts_id) ORDER BY d.parts_id ASC) parts_id,\n" +
				"	group_concat(DISTINCT concat(p.parts_name, '[' , p.parts_code, ']') ORDER BY p.parts_name ASC, p.parts_code ASC SEPARATOR '，') parts_name\n" +
				"FROM bs_repair_check_detail d\n" +
				"LEFT JOIN bd_device_parts p ON d.parts_id = p.parts_id\n" +
				"where d.log_id = ?\n" +
				"GROUP BY d.log_id, d.fault_id";
		return Db.find(sql, logId);
	}
	
	public void delDetail(String logId, String faultId){
		String sql = "delete from bs_repair_check_detail where log_id = ?";
		if(Utils.isNotEmpty(faultId)){
			sql += " and fault_id = " + faultId;
		}
		Db.update(sql, logId);
		super.setCRUD(optMenu, "D", "删除检测记录明细,log_id：" + logId);
	}
	
	public List<Record> getDetail(String logId, String faultId){
		String sql = "select * from bs_repair_check_detail where log_id = ?";
		if(Utils.isNotEmpty(faultId)){
			sql += " and fault_id = " + faultId;
		}
		return Db.find(sql, logId);
	}

	/*
	 * 查询订单未检查数量
	 */
	public Integer getUnInspectsNum(String orderId) {
		String sql = "select count(oe.equ_id) from bs_order_equipment oe \n" +
				"left join bs_repair_reg_log rl on oe.order_id = rl.order_id and oe.equ_id = rl.equ_id \n" +
				"where oe.order_id = ?\n" +
				"and rl.log_id is null";
		Long num = Db.queryLong(sql, orderId);
		return num.intValue();
	}

}
